{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task\n",
    "\n",
    "This notebook produces Table 1 and accompanying numbers in Section 2 of the paper. It uses data from the OECD and the Human Mortality DataBase. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np \n",
    "from matplotlib import pyplot as plt\n",
    "import pandas as pd \n",
    "from linearmodels import PanelOLS as panreg"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data\n",
    "\n",
    "We use data from the OECD for health care costs, income per capita and population structure. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_stata('../data_sources/oecd/oecd_history.dta',convert_categoricals=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We compile from the Human Mortality DataBase data on life expectancy at birth for each country. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "le = pd.read_stata('../data_sources/hmd/leatbirth.dta',convert_categoricals=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We merge both datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(le,left_on=['country','year'],right_on=['country','year'],how='left')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We map country names to country codes we assigned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "map_c = {19:'dk',16:'fr',12:'de',17:'it',14:'nl',15:'sp',13:'se',9:'uk',10:'us'}\n",
    "df['cshort'] = df['cid'].replace(map_c)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We select countries we want to work with. Note that Italy does not have data from OECD going far enough in past for this exercise. Therefore, we do not use Italy. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df.cid.isin([10,12,13,14,15,16,19])]\n",
    "countries = ['dk','fr','nl','se','de','sp','us']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We set the period of analysis from 1970 to 2007"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "start_yr = 1970\n",
    "stop_yr = 2007\n",
    "df = df[(df.year>=start_yr) & (df.year<=stop_yr)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For France, data is available every 5 years. Therefore we interpolate linearly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "for c in ['fr']:\n",
    "\tdf.loc[df['cshort']==c,'tothlthcpoecdcap'] = df.loc[df['cshort']==c,'tothlthcpoecdcap'].interpolate()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We create the key variables for our analysis. Everything is in real national currencies. We compute growth rates within countries, so currencies have no effect. When we do averages at the end we do averages of growth rates, so currencies do not have effects either. We compute logs to take differences later and do regressions. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.rename({'tothlthcpoecdcap':'pm'},axis=1)\n",
    "df['log_pm'] = np.log(df['pm']) \n",
    "df = df.rename({'gdp15ncucap':'y'},axis=1)\n",
    "df['log_y'] = np.log(df['y'])\n",
    "df = df.rename({'propop65p':'age65p'},axis=1)\n",
    "df['log_age65p'] = np.log(df['age65p'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We create linear splines by decades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['year_70'] = np.where(df['year']>=1970,np.where(df['year']<=1979,df['year']-1969,1979-1969),0)\n",
    "df['year_80'] = np.where(df['year']>=1980,np.where(df['year']<=1989,df['year']-1979,1989-1979),0)\n",
    "df['year_90'] = np.where(df['year']>=1990,np.where(df['year']<=1999,df['year']-1989,1999-1989),0)\n",
    "df['year_00'] = np.where(df['year']>=2000,np.where(df['year']<=stop_yr,df['year']-1999,stop_yr-1999),0)\n",
    "splines = ['year_70','year_80','year_90','year_00']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We set the index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.set_index(['cshort','year'],inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looking at stats. Some missings here and there for some countries, at beginning of observation period and Germany in 1991 (reunification). We skip those observations in regressions. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>log_pm</th>\n",
       "      <td>262.0</td>\n",
       "      <td>8.285431</td>\n",
       "      <td>1.201817</td>\n",
       "      <td>5.787308</td>\n",
       "      <td>7.442631</td>\n",
       "      <td>7.941147</td>\n",
       "      <td>9.703233</td>\n",
       "      <td>10.410534</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>log_y</th>\n",
       "      <td>266.0</td>\n",
       "      <td>10.822299</td>\n",
       "      <td>1.105787</td>\n",
       "      <td>9.240627</td>\n",
       "      <td>10.047000</td>\n",
       "      <td>10.337869</td>\n",
       "      <td>12.232550</td>\n",
       "      <td>12.929209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>log_age65p</th>\n",
       "      <td>266.0</td>\n",
       "      <td>2.631439</td>\n",
       "      <td>0.159236</td>\n",
       "      <td>2.251292</td>\n",
       "      <td>2.525729</td>\n",
       "      <td>2.639057</td>\n",
       "      <td>2.740840</td>\n",
       "      <td>2.985682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year_70</th>\n",
       "      <td>266.0</td>\n",
       "      <td>8.815789</td>\n",
       "      <td>2.473996</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year_80</th>\n",
       "      <td>266.0</td>\n",
       "      <td>6.184211</td>\n",
       "      <td>4.396385</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>9.500000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year_90</th>\n",
       "      <td>266.0</td>\n",
       "      <td>3.552632</td>\n",
       "      <td>4.316604</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year_00</th>\n",
       "      <td>266.0</td>\n",
       "      <td>0.947368</td>\n",
       "      <td>2.118439</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            count       mean       std       min        25%        50%  \\\n",
       "log_pm      262.0   8.285431  1.201817  5.787308   7.442631   7.941147   \n",
       "log_y       266.0  10.822299  1.105787  9.240627  10.047000  10.337869   \n",
       "log_age65p  266.0   2.631439  0.159236  2.251292   2.525729   2.639057   \n",
       "year_70     266.0   8.815789  2.473996  1.000000  10.000000  10.000000   \n",
       "year_80     266.0   6.184211  4.396385  0.000000   0.000000   9.500000   \n",
       "year_90     266.0   3.552632  4.316604  0.000000   0.000000   0.000000   \n",
       "year_00     266.0   0.947368  2.118439  0.000000   0.000000   0.000000   \n",
       "\n",
       "                  75%        max  \n",
       "log_pm       9.703233  10.410534  \n",
       "log_y       12.232550  12.929209  \n",
       "log_age65p   2.740840   2.985682  \n",
       "year_70     10.000000  10.000000  \n",
       "year_80     10.000000  10.000000  \n",
       "year_90      9.000000  10.000000  \n",
       "year_00      0.000000   8.000000  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['log_pm','log_y','log_age65p','year_70','year_80','year_90','year_00']].describe().transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>proptotgdp</th>\n",
       "      <th>tothlth</th>\n",
       "      <th>tothlthppp</th>\n",
       "      <th>tothlthcpoecd</th>\n",
       "      <th>tothltcpoecdppp</th>\n",
       "      <th>tothlthcap</th>\n",
       "      <th>tothlthpppcap</th>\n",
       "      <th>pm</th>\n",
       "      <th>tothltcpoecdpppcap</th>\n",
       "      <th>...</th>\n",
       "      <th>propemp</th>\n",
       "      <th>cid</th>\n",
       "      <th>leatbirth</th>\n",
       "      <th>log_pm</th>\n",
       "      <th>log_y</th>\n",
       "      <th>log_age65p</th>\n",
       "      <th>year_70</th>\n",
       "      <th>year_80</th>\n",
       "      <th>year_90</th>\n",
       "      <th>year_00</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cshort</th>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>dk</th>\n",
       "      <th>1970</th>\n",
       "      <td>Denmark</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>48.9</td>\n",
       "      <td>19.0</td>\n",
       "      <td>73.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.107184</td>\n",
       "      <td>2.493205</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>de</th>\n",
       "      <th>1991</th>\n",
       "      <td>Germany</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>75.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.228964</td>\n",
       "      <td>2.701361</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">nl</th>\n",
       "      <th>1970</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>73.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.850551</td>\n",
       "      <td>2.312535</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1971</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>73.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.881058</td>\n",
       "      <td>2.322388</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 54 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 country  proptotgdp  tothlth  tothlthppp  tothlthcpoecd  \\\n",
       "cshort year                                                                \n",
       "dk     1970      Denmark         NaN      NaN         NaN            NaN   \n",
       "de     1991      Germany         NaN      NaN         NaN            NaN   \n",
       "nl     1970  Netherlands         NaN      NaN         NaN            NaN   \n",
       "       1971  Netherlands         NaN      NaN         NaN            NaN   \n",
       "\n",
       "             tothltcpoecdppp  tothlthcap  tothlthpppcap  pm  \\\n",
       "cshort year                                                   \n",
       "dk     1970              NaN         NaN            NaN NaN   \n",
       "de     1991              NaN         NaN            NaN NaN   \n",
       "nl     1970              NaN         NaN            NaN NaN   \n",
       "       1971              NaN         NaN            NaN NaN   \n",
       "\n",
       "             tothltcpoecdpppcap  ...  propemp   cid  leatbirth  log_pm  \\\n",
       "cshort year                      ...                                     \n",
       "dk     1970                 NaN  ...     48.9  19.0       73.3     NaN   \n",
       "de     1991                 NaN  ...      NaN  12.0       75.5     NaN   \n",
       "nl     1970                 NaN  ...      NaN  14.0       73.7     NaN   \n",
       "       1971                 NaN  ...      NaN  14.0       73.9     NaN   \n",
       "\n",
       "                 log_y  log_age65p  year_70  year_80  year_90  year_00  \n",
       "cshort year                                                             \n",
       "dk     1970  12.107184    2.493205        1        0        0        0  \n",
       "de     1991  10.228964    2.701361       10       10        2        0  \n",
       "nl     1970   9.850551    2.312535        1        0        0        0  \n",
       "       1971   9.881058    2.322388        2        0        0        0  \n",
       "\n",
       "[4 rows x 54 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['log_pm'].isna(),:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = df[['log_pm','log_y','log_age65p','year_70','year_80','year_90','year_00']].dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We do a linear regression with countries fixed effects. This is a log-log specification so that coefficients measure elasticities. These will be used later. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = panreg(data['log_pm'], data[['log_y','log_age65p','year_70','year_80','year_90','year_00']], entity_effects = True).fit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"simpletable\">\n",
       "<caption>PanelOLS Estimation Summary</caption>\n",
       "<tr>\n",
       "  <th>Dep. Variable:</th>         <td>log_pm</td>      <th>  R-squared:         </th>    <td>0.9276</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Estimator:</th>            <td>PanelOLS</td>     <th>  R-squared (Between):</th>   <td>0.8161</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>No. Observations:</th>        <td>262</td>       <th>  R-squared (Within):</th>    <td>0.9276</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Date:</th>             <td>Wed, Dec 01 2021</td> <th>  R-squared (Overall):</th>   <td>0.8164</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Time:</th>                 <td>08:47:18</td>     <th>  Log-likelihood     </th>    <td>238.88</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Cov. Estimator:</th>      <td>Unadjusted</td>    <th>                     </th>       <td></td>    \n",
       "</tr>\n",
       "<tr>\n",
       "  <th></th>                          <td></td>         <th>  F-statistic:       </th>    <td>531.69</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Entities:</th>                 <td>7</td>        <th>  P-value            </th>    <td>0.0000</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Avg Obs:</th>               <td>37.429</td>      <th>  Distribution:      </th>   <td>F(6,249)</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Min Obs:</th>               <td>36.000</td>      <th>                     </th>       <td></td>    \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Max Obs:</th>               <td>38.000</td>      <th>  F-statistic (robust):</th>  <td>531.69</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th></th>                          <td></td>         <th>  P-value            </th>    <td>0.0000</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Time periods:</th>            <td>38</td>        <th>  Distribution:      </th>   <td>F(6,249)</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Avg Obs:</th>               <td>6.8947</td>      <th>                     </th>       <td></td>    \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Min Obs:</th>               <td>5.0000</td>      <th>                     </th>       <td></td>    \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Max Obs:</th>               <td>7.0000</td>      <th>                     </th>       <td></td>    \n",
       "</tr>\n",
       "<tr>\n",
       "  <th></th>                          <td></td>         <th>                     </th>       <td></td>    \n",
       "</tr>\n",
       "</table>\n",
       "<table class=\"simpletable\">\n",
       "<caption>Parameter Estimates</caption>\n",
       "<tr>\n",
       "       <td></td>      <th>Parameter</th> <th>Std. Err.</th> <th>T-stat</th> <th>P-value</th> <th>Lower CI</th> <th>Upper CI</th>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>log_y</th>       <td>0.9008</td>    <td>0.1152</td>   <td>7.8201</td> <td>0.0000</td>   <td>0.6739</td>   <td>1.1277</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>log_age65p</th>  <td>0.7043</td>    <td>0.1107</td>   <td>6.3628</td> <td>0.0000</td>   <td>0.4863</td>   <td>0.9223</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year_70</th>     <td>0.0227</td>    <td>0.0044</td>   <td>5.1292</td> <td>0.0000</td>   <td>0.0140</td>   <td>0.0315</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year_80</th>     <td>0.0009</td>    <td>0.0033</td>   <td>0.2869</td> <td>0.7744</td>   <td>-0.0055</td>  <td>0.0074</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year_90</th>     <td>0.0106</td>    <td>0.0033</td>   <td>3.2422</td> <td>0.0013</td>   <td>0.0041</td>   <td>0.0170</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year_00</th>     <td>0.0112</td>    <td>0.0046</td>   <td>2.4244</td> <td>0.0160</td>   <td>0.0021</td>   <td>0.0203</td> \n",
       "</tr>\n",
       "</table><br/><br/>F-test for Poolability: 114.87<br/>P-value: 0.0000<br/>Distribution: F(6,249)<br/><br/>Included effects: Entity<br/>id: 0x7faae5bfc6d0"
      ],
      "text/plain": [
       "                          PanelOLS Estimation Summary                           \n",
       "================================================================================\n",
       "Dep. Variable:                 log_pm   R-squared:                        0.9276\n",
       "Estimator:                   PanelOLS   R-squared (Between):              0.8161\n",
       "No. Observations:                 262   R-squared (Within):               0.9276\n",
       "Date:                Wed, Dec 01 2021   R-squared (Overall):              0.8164\n",
       "Time:                        08:47:18   Log-likelihood                    238.88\n",
       "Cov. Estimator:            Unadjusted                                           \n",
       "                                        F-statistic:                      531.69\n",
       "Entities:                           7   P-value                           0.0000\n",
       "Avg Obs:                       37.429   Distribution:                   F(6,249)\n",
       "Min Obs:                       36.000                                           \n",
       "Max Obs:                       38.000   F-statistic (robust):             531.69\n",
       "                                        P-value                           0.0000\n",
       "Time periods:                      38   Distribution:                   F(6,249)\n",
       "Avg Obs:                       6.8947                                           \n",
       "Min Obs:                       5.0000                                           \n",
       "Max Obs:                       7.0000                                           \n",
       "                                                                                \n",
       "                             Parameter Estimates                              \n",
       "==============================================================================\n",
       "            Parameter  Std. Err.     T-stat    P-value    Lower CI    Upper CI\n",
       "------------------------------------------------------------------------------\n",
       "log_y          0.9008     0.1152     7.8201     0.0000      0.6739      1.1277\n",
       "log_age65p     0.7043     0.1107     6.3628     0.0000      0.4863      0.9223\n",
       "year_70        0.0227     0.0044     5.1292     0.0000      0.0140      0.0315\n",
       "year_80        0.0009     0.0033     0.2869     0.7744     -0.0055      0.0074\n",
       "year_90        0.0106     0.0033     3.2422     0.0013      0.0041      0.0170\n",
       "year_00        0.0112     0.0046     2.4244     0.0160      0.0021      0.0203\n",
       "==============================================================================\n",
       "\n",
       "F-test for Poolability: 114.87\n",
       "P-value: 0.0000\n",
       "Distribution: F(6,249)\n",
       "\n",
       "Included effects: Entity\n",
       "PanelEffectsResults, id: 0x7faae5bfc6d0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We compute residuals, including the fixed effect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['fe'] = results.estimated_effects\n",
    "df['resid'] = results.resids\n",
    "df['resid_fe'] = df['fe'] + df['resid']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We keep elasticities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.9007917236030678, 0.7043116165974213)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "epy = results.params['log_y']\n",
    "epa = results.params['log_age65p']\n",
    "epy, epa"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We pivot the data to compute growth rates more easily"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"7\" halign=\"left\">age65p</th>\n",
       "      <th colspan=\"3\" halign=\"left\">cid</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"3\" halign=\"left\">year_80</th>\n",
       "      <th colspan=\"7\" halign=\"left\">year_90</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cshort</th>\n",
       "      <th>de</th>\n",
       "      <th>dk</th>\n",
       "      <th>fr</th>\n",
       "      <th>nl</th>\n",
       "      <th>se</th>\n",
       "      <th>sp</th>\n",
       "      <th>us</th>\n",
       "      <th>de</th>\n",
       "      <th>dk</th>\n",
       "      <th>fr</th>\n",
       "      <th>...</th>\n",
       "      <th>se</th>\n",
       "      <th>sp</th>\n",
       "      <th>us</th>\n",
       "      <th>de</th>\n",
       "      <th>dk</th>\n",
       "      <th>fr</th>\n",
       "      <th>nl</th>\n",
       "      <th>se</th>\n",
       "      <th>sp</th>\n",
       "      <th>us</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1970</th>\n",
       "      <td>13.0</td>\n",
       "      <td>12.1</td>\n",
       "      <td>12.9</td>\n",
       "      <td>10.1</td>\n",
       "      <td>13.5</td>\n",
       "      <td>9.5</td>\n",
       "      <td>9.8</td>\n",
       "      <td>12.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1971</th>\n",
       "      <td>13.3</td>\n",
       "      <td>12.3</td>\n",
       "      <td>13.0</td>\n",
       "      <td>10.2</td>\n",
       "      <td>13.7</td>\n",
       "      <td>9.6</td>\n",
       "      <td>9.9</td>\n",
       "      <td>12.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1972</th>\n",
       "      <td>13.5</td>\n",
       "      <td>12.5</td>\n",
       "      <td>13.1</td>\n",
       "      <td>10.3</td>\n",
       "      <td>14.1</td>\n",
       "      <td>9.7</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1973</th>\n",
       "      <td>13.7</td>\n",
       "      <td>12.7</td>\n",
       "      <td>13.2</td>\n",
       "      <td>10.4</td>\n",
       "      <td>14.3</td>\n",
       "      <td>9.9</td>\n",
       "      <td>10.2</td>\n",
       "      <td>12.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1974</th>\n",
       "      <td>14.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>13.3</td>\n",
       "      <td>10.5</td>\n",
       "      <td>14.6</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.3</td>\n",
       "      <td>12.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 392 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       age65p                                       cid              ...  \\\n",
       "cshort     de    dk    fr    nl    se    sp    us    de    dk    fr  ...   \n",
       "year                                                                 ...   \n",
       "1970     13.0  12.1  12.9  10.1  13.5   9.5   9.8  12.0  19.0  16.0  ...   \n",
       "1971     13.3  12.3  13.0  10.2  13.7   9.6   9.9  12.0  19.0  16.0  ...   \n",
       "1972     13.5  12.5  13.1  10.3  14.1   9.7  10.0  12.0  19.0  16.0  ...   \n",
       "1973     13.7  12.7  13.2  10.4  14.3   9.9  10.2  12.0  19.0  16.0  ...   \n",
       "1974     14.0  13.0  13.3  10.5  14.6  10.0  10.3  12.0  19.0  16.0  ...   \n",
       "\n",
       "       year_80       year_90                    \n",
       "cshort      se sp us      de dk fr nl se sp us  \n",
       "year                                            \n",
       "1970         0  0  0       0  0  0  0  0  0  0  \n",
       "1971         0  0  0       0  0  0  0  0  0  0  \n",
       "1972         0  0  0       0  0  0  0  0  0  0  \n",
       "1973         0  0  0       0  0  0  0  0  0  0  \n",
       "1974         0  0  0       0  0  0  0  0  0  0  \n",
       "\n",
       "[5 rows x 392 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tab = pd.pivot_table(df,index='year',columns='cshort')\n",
    "tab.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We create a decades categorical variable to check on growth rates of the residual by decade. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "tab['decades'] = pd.cut(tab.index,bins=[1969,1979,1989,1999,2019])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We first check total real growth rates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"7\" halign=\"left\">log_pm</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cshort</th>\n",
       "      <th>de</th>\n",
       "      <th>dk</th>\n",
       "      <th>fr</th>\n",
       "      <th>nl</th>\n",
       "      <th>se</th>\n",
       "      <th>sp</th>\n",
       "      <th>us</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>decades</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>(1969, 1979]</th>\n",
       "      <td>0.062477</td>\n",
       "      <td>0.030926</td>\n",
       "      <td>0.056782</td>\n",
       "      <td>0.040381</td>\n",
       "      <td>0.051545</td>\n",
       "      <td>0.077221</td>\n",
       "      <td>0.049176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(1979, 1989]</th>\n",
       "      <td>0.019741</td>\n",
       "      <td>0.011621</td>\n",
       "      <td>0.036308</td>\n",
       "      <td>0.013965</td>\n",
       "      <td>0.011078</td>\n",
       "      <td>0.038355</td>\n",
       "      <td>0.049442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(1989, 1999]</th>\n",
       "      <td>0.026060</td>\n",
       "      <td>0.022027</td>\n",
       "      <td>0.034124</td>\n",
       "      <td>0.036587</td>\n",
       "      <td>0.007186</td>\n",
       "      <td>0.041565</td>\n",
       "      <td>0.032766</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(1999, 2019]</th>\n",
       "      <td>0.014876</td>\n",
       "      <td>0.032576</td>\n",
       "      <td>0.022151</td>\n",
       "      <td>0.035701</td>\n",
       "      <td>0.034924</td>\n",
       "      <td>0.043396</td>\n",
       "      <td>0.039996</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                log_pm                                                    \\\n",
       "cshort              de        dk        fr        nl        se        sp   \n",
       "decades                                                                    \n",
       "(1969, 1979]  0.062477  0.030926  0.056782  0.040381  0.051545  0.077221   \n",
       "(1979, 1989]  0.019741  0.011621  0.036308  0.013965  0.011078  0.038355   \n",
       "(1989, 1999]  0.026060  0.022027  0.034124  0.036587  0.007186  0.041565   \n",
       "(1999, 2019]  0.014876  0.032576  0.022151  0.035701  0.034924  0.043396   \n",
       "\n",
       "                        \n",
       "cshort              us  \n",
       "decades                 \n",
       "(1969, 1979]  0.049176  \n",
       "(1979, 1989]  0.049442  \n",
       "(1989, 1999]  0.032766  \n",
       "(1999, 2019]  0.039996  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gr = tab.loc[:,['log_pm','decades']]\n",
    "for c in countries:\n",
    "\tgr.loc[:,('log_pm',c)] = gr.loc[:,('log_pm',c)]-gr.loc[:,('log_pm',c)].shift(1)\n",
    "gr.groupby(['decades']).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We then look at residual growth rates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"7\" halign=\"left\">resid_fe</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cshort</th>\n",
       "      <th>de</th>\n",
       "      <th>dk</th>\n",
       "      <th>fr</th>\n",
       "      <th>nl</th>\n",
       "      <th>se</th>\n",
       "      <th>sp</th>\n",
       "      <th>us</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>decades</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>(1969, 1979]</th>\n",
       "      <td>-0.000216</td>\n",
       "      <td>-0.022284</td>\n",
       "      <td>-0.000431</td>\n",
       "      <td>-0.011773</td>\n",
       "      <td>0.000855</td>\n",
       "      <td>0.020510</td>\n",
       "      <td>-0.006110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(1979, 1989]</th>\n",
       "      <td>0.003066</td>\n",
       "      <td>-0.012068</td>\n",
       "      <td>0.020095</td>\n",
       "      <td>-0.008641</td>\n",
       "      <td>-0.015238</td>\n",
       "      <td>0.003016</td>\n",
       "      <td>0.022180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(1989, 1999]</th>\n",
       "      <td>-0.002119</td>\n",
       "      <td>-0.004249</td>\n",
       "      <td>-0.001429</td>\n",
       "      <td>-0.002532</td>\n",
       "      <td>-0.013894</td>\n",
       "      <td>-0.004401</td>\n",
       "      <td>0.004201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(1999, 2019]</th>\n",
       "      <td>-0.029177</td>\n",
       "      <td>0.005006</td>\n",
       "      <td>-0.005675</td>\n",
       "      <td>0.002037</td>\n",
       "      <td>-0.000953</td>\n",
       "      <td>0.011922</td>\n",
       "      <td>0.012548</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              resid_fe                                                    \\\n",
       "cshort              de        dk        fr        nl        se        sp   \n",
       "decades                                                                    \n",
       "(1969, 1979] -0.000216 -0.022284 -0.000431 -0.011773  0.000855  0.020510   \n",
       "(1979, 1989]  0.003066 -0.012068  0.020095 -0.008641 -0.015238  0.003016   \n",
       "(1989, 1999] -0.002119 -0.004249 -0.001429 -0.002532 -0.013894 -0.004401   \n",
       "(1999, 2019] -0.029177  0.005006 -0.005675  0.002037 -0.000953  0.011922   \n",
       "\n",
       "                        \n",
       "cshort              us  \n",
       "decades                 \n",
       "(1969, 1979] -0.006110  \n",
       "(1979, 1989]  0.022180  \n",
       "(1989, 1999]  0.004201  \n",
       "(1999, 2019]  0.012548  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gr = tab.loc[:,['resid_fe','decades']]\n",
    "for c in countries:\n",
    "\tgr.loc[:,('resid_fe',c)] = gr.loc[:,('resid_fe',c)]-gr.loc[:,('resid_fe',c)].shift(1)\n",
    "gr.groupby(['decades']).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We create two periods with three years close to 1970 and three years close to 2005. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "tab['period'] = np.nan\n",
    "tab.loc[:,'period'] = np.where((tab.index>=start_yr) & (tab.index<=start_yr+2),0,tab.loc[:,'period'])\n",
    "tab.loc[:,'period'] = np.where((tab.index>=stop_yr-2) & (tab.index<=stop_yr),1,tab.loc[:,'period'])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We set the residual in levels (not used)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "tab = tab.loc[~tab.period.isna(),:]\n",
    "for c in countries:\n",
    "\ttab.loc[:,('resid_fe',c)] = np.exp(tab.loc[:,('resid_fe',c)])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We select what we will look at"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "tab = tab.loc[:,['pm','y','age65p','resid_fe','totpop','leatbirth','period']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We do means of both pre and post periods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/loulou/.local/lib/python3.8/site-packages/pandas/core/generic.py:4150: PerformanceWarning: dropping on a non-lexsorted multi-index without a level parameter may impact performance.\n",
      "  obj = obj._drop_axis(labels, axis, level=level, errors=errors)\n"
     ]
    }
   ],
   "source": [
    "comp = tab.groupby('period').mean()\n",
    "comp.index=['1970','2005']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We compute average growth rates between pre and post periods for various outcomes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "nyears = stop_yr - start_yr\n",
    "for o in ['pm','y','age65p','resid_fe','leatbirth']:\n",
    "\tfor c in countries:\n",
    "\t\tcomp.loc['total',(o,c)] = (comp.loc['2005',(o,c)]/comp.loc['1970',(o,c)])**(1/nyears)-1.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"7\" halign=\"left\">pm</th>\n",
       "      <th colspan=\"3\" halign=\"left\">y</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"3\" halign=\"left\">totpop</th>\n",
       "      <th colspan=\"7\" halign=\"left\">leatbirth</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cshort</th>\n",
       "      <th>de</th>\n",
       "      <th>dk</th>\n",
       "      <th>fr</th>\n",
       "      <th>nl</th>\n",
       "      <th>se</th>\n",
       "      <th>sp</th>\n",
       "      <th>us</th>\n",
       "      <th>de</th>\n",
       "      <th>dk</th>\n",
       "      <th>fr</th>\n",
       "      <th>...</th>\n",
       "      <th>se</th>\n",
       "      <th>sp</th>\n",
       "      <th>us</th>\n",
       "      <th>de</th>\n",
       "      <th>dk</th>\n",
       "      <th>fr</th>\n",
       "      <th>nl</th>\n",
       "      <th>se</th>\n",
       "      <th>sp</th>\n",
       "      <th>us</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1970</th>\n",
       "      <td>1224.219333</td>\n",
       "      <td>14655.579500</td>\n",
       "      <td>897.815600</td>\n",
       "      <td>1279.080000</td>\n",
       "      <td>14005.546000</td>\n",
       "      <td>381.980667</td>\n",
       "      <td>1754.458667</td>\n",
       "      <td>21257.766667</td>\n",
       "      <td>185997.833333</td>\n",
       "      <td>16524.300000</td>\n",
       "      <td>...</td>\n",
       "      <td>8087.800000</td>\n",
       "      <td>34214.366667</td>\n",
       "      <td>207536.3</td>\n",
       "      <td>70.800000</td>\n",
       "      <td>73.333333</td>\n",
       "      <td>72.233333</td>\n",
       "      <td>73.800000</td>\n",
       "      <td>74.800000</td>\n",
       "      <td>72.000000</td>\n",
       "      <td>71.133333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005</th>\n",
       "      <td>3371.977667</td>\n",
       "      <td>32191.699000</td>\n",
       "      <td>3279.448333</td>\n",
       "      <td>3638.045000</td>\n",
       "      <td>32271.261667</td>\n",
       "      <td>1990.356333</td>\n",
       "      <td>7912.289000</td>\n",
       "      <td>32962.200000</td>\n",
       "      <td>357608.766667</td>\n",
       "      <td>32392.566667</td>\n",
       "      <td>...</td>\n",
       "      <td>9086.066667</td>\n",
       "      <td>44425.766667</td>\n",
       "      <td>298375.9</td>\n",
       "      <td>79.766667</td>\n",
       "      <td>78.366667</td>\n",
       "      <td>80.866667</td>\n",
       "      <td>79.900000</td>\n",
       "      <td>80.933333</td>\n",
       "      <td>80.866667</td>\n",
       "      <td>77.833333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total</th>\n",
       "      <td>0.027762</td>\n",
       "      <td>0.021495</td>\n",
       "      <td>0.035633</td>\n",
       "      <td>0.028654</td>\n",
       "      <td>0.022817</td>\n",
       "      <td>0.045624</td>\n",
       "      <td>0.041550</td>\n",
       "      <td>0.011926</td>\n",
       "      <td>0.017825</td>\n",
       "      <td>0.018358</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.003228</td>\n",
       "      <td>0.001796</td>\n",
       "      <td>0.003056</td>\n",
       "      <td>0.002149</td>\n",
       "      <td>0.002132</td>\n",
       "      <td>0.003144</td>\n",
       "      <td>0.002436</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 pm                                                        \\\n",
       "cshort           de            dk           fr           nl            se   \n",
       "1970    1224.219333  14655.579500   897.815600  1279.080000  14005.546000   \n",
       "2005    3371.977667  32191.699000  3279.448333  3638.045000  32271.261667   \n",
       "total      0.027762      0.021495     0.035633     0.028654      0.022817   \n",
       "\n",
       "                                             y                               \\\n",
       "cshort           sp           us            de             dk            fr   \n",
       "1970     381.980667  1754.458667  21257.766667  185997.833333  16524.300000   \n",
       "2005    1990.356333  7912.289000  32962.200000  357608.766667  32392.566667   \n",
       "total      0.045624     0.041550      0.011926       0.017825      0.018358   \n",
       "\n",
       "        ...       totpop                          leatbirth             \\\n",
       "cshort  ...           se            sp        us         de         dk   \n",
       "1970    ...  8087.800000  34214.366667  207536.3  70.800000  73.333333   \n",
       "2005    ...  9086.066667  44425.766667  298375.9  79.766667  78.366667   \n",
       "total   ...          NaN           NaN       NaN   0.003228   0.001796   \n",
       "\n",
       "                                                               \n",
       "cshort         fr         nl         se         sp         us  \n",
       "1970    72.233333  73.800000  74.800000  72.000000  71.133333  \n",
       "2005    80.866667  79.900000  80.933333  80.866667  77.833333  \n",
       "total    0.003056   0.002149   0.002132   0.003144   0.002436  \n",
       "\n",
       "[3 rows x 42 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We setup Table 1. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "table = pd.DataFrame(index=['GDP growth','Pop Aging','Unexplained','Total'],columns=countries)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Total growth can be read directly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/loulou/.local/lib/python3.8/site-packages/pandas/core/indexing.py:925: PerformanceWarning: indexing past lexsort depth may impact performance.\n",
      "  return self._getitem_tuple(key)\n"
     ]
    }
   ],
   "source": [
    "table.loc['Total',:] = comp.loc['total',('pm',)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we compute growth for each component of Table 1 using elasticities and growth rates of the various variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "table.loc['GDP growth',:] = comp.loc['total',('y',)]*epy\n",
    "table.loc['Pop Aging',:] = comp.loc['total',('age65p',)]*epa\n",
    "table.loc['Unexplained',:] = table.loc['Total',:] - table.loc['Pop Aging',:] - table.loc['GDP growth',:]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is the data for Table 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dk</th>\n",
       "      <th>fr</th>\n",
       "      <th>nl</th>\n",
       "      <th>se</th>\n",
       "      <th>de</th>\n",
       "      <th>sp</th>\n",
       "      <th>us</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>GDP growth</th>\n",
       "      <td>0.016056</td>\n",
       "      <td>0.016537</td>\n",
       "      <td>0.017302</td>\n",
       "      <td>0.016312</td>\n",
       "      <td>0.010743</td>\n",
       "      <td>0.019754</td>\n",
       "      <td>0.017838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pop Aging</th>\n",
       "      <td>0.003957</td>\n",
       "      <td>0.00486</td>\n",
       "      <td>0.006416</td>\n",
       "      <td>0.004288</td>\n",
       "      <td>0.007105</td>\n",
       "      <td>0.010307</td>\n",
       "      <td>0.004453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unexplained</th>\n",
       "      <td>0.001482</td>\n",
       "      <td>0.014236</td>\n",
       "      <td>0.004936</td>\n",
       "      <td>0.002216</td>\n",
       "      <td>0.009914</td>\n",
       "      <td>0.015562</td>\n",
       "      <td>0.019259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>0.021495</td>\n",
       "      <td>0.035633</td>\n",
       "      <td>0.028654</td>\n",
       "      <td>0.022817</td>\n",
       "      <td>0.027762</td>\n",
       "      <td>0.045624</td>\n",
       "      <td>0.04155</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   dk        fr        nl        se        de        sp  \\\n",
       "GDP growth   0.016056  0.016537  0.017302  0.016312  0.010743  0.019754   \n",
       "Pop Aging    0.003957   0.00486  0.006416  0.004288  0.007105  0.010307   \n",
       "Unexplained  0.001482  0.014236  0.004936  0.002216  0.009914  0.015562   \n",
       "Total        0.021495  0.035633  0.028654  0.022817  0.027762  0.045624   \n",
       "\n",
       "                   us  \n",
       "GDP growth   0.017838  \n",
       "Pop Aging    0.004453  \n",
       "Unexplained  0.019259  \n",
       "Total         0.04155  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We add the column with European population averaged rates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/loulou/.local/lib/python3.8/site-packages/pandas/core/indexing.py:925: PerformanceWarning: indexing past lexsort depth may impact performance.\n",
      "  return self._getitem_tuple(key)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dk</th>\n",
       "      <th>fr</th>\n",
       "      <th>nl</th>\n",
       "      <th>se</th>\n",
       "      <th>de</th>\n",
       "      <th>sp</th>\n",
       "      <th>us</th>\n",
       "      <th>eu</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>GDP growth</th>\n",
       "      <td>0.016056</td>\n",
       "      <td>0.016537</td>\n",
       "      <td>0.017302</td>\n",
       "      <td>0.016312</td>\n",
       "      <td>0.010743</td>\n",
       "      <td>0.019754</td>\n",
       "      <td>0.017838</td>\n",
       "      <td>0.015060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pop Aging</th>\n",
       "      <td>0.003957</td>\n",
       "      <td>0.00486</td>\n",
       "      <td>0.006416</td>\n",
       "      <td>0.004288</td>\n",
       "      <td>0.007105</td>\n",
       "      <td>0.010307</td>\n",
       "      <td>0.004453</td>\n",
       "      <td>0.006860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unexplained</th>\n",
       "      <td>0.001482</td>\n",
       "      <td>0.014236</td>\n",
       "      <td>0.004936</td>\n",
       "      <td>0.002216</td>\n",
       "      <td>0.009914</td>\n",
       "      <td>0.015562</td>\n",
       "      <td>0.019259</td>\n",
       "      <td>0.011397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>0.021495</td>\n",
       "      <td>0.035633</td>\n",
       "      <td>0.028654</td>\n",
       "      <td>0.022817</td>\n",
       "      <td>0.027762</td>\n",
       "      <td>0.045624</td>\n",
       "      <td>0.04155</td>\n",
       "      <td>0.033317</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   dk        fr        nl        se        de        sp  \\\n",
       "GDP growth   0.016056  0.016537  0.017302  0.016312  0.010743  0.019754   \n",
       "Pop Aging    0.003957   0.00486  0.006416  0.004288  0.007105  0.010307   \n",
       "Unexplained  0.001482  0.014236  0.004936  0.002216  0.009914  0.015562   \n",
       "Total        0.021495  0.035633  0.028654  0.022817  0.027762  0.045624   \n",
       "\n",
       "                   us        eu  \n",
       "GDP growth   0.017838  0.015060  \n",
       "Pop Aging    0.004453  0.006860  \n",
       "Unexplained  0.019259  0.011397  \n",
       "Total         0.04155  0.033317  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eu_countries = [c for c in countries if c!='us']\n",
    "pop_eu = comp.loc['2005',('totpop',)]\n",
    "pop_eu = pop_eu[eu_countries]\n",
    "pop_eu = pop_eu/pop_eu.sum()\n",
    "for i in table.index:\n",
    "\ttable.loc[i,'eu'] = np.sum(table.loc[i,eu_countries]*pop_eu)\n",
    "table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in table.columns:\n",
    "\ttable[i] = table[i].astype('float64')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table 1\n",
    "\n",
    "This is Table 1 in the paper"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "table.loc[table.index!='Total',['de','dk','fr','nl','se','sp','us','eu']].round(3).to_latex('../tables/tab_1_growth.tex')"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "cf2a50979671a58939829e6829efb726aa5da11149213b77bd50351f899d04fb"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
